import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;


public class OracleNormalTemplate extends Template {
	
	private Connection connection;
	private Date lshipdate = null;
	private String rname = "";
	private String psize = "";
	private String ptype = "";
	private Date oorderdate = null;
	private Date oorderdate2 = null;
	private String cmktsegment = "";
	@Override
	public void configure(Map<String, Object> params) {
		setConnection((Connection) params.get("connection"));
	}
	public  Map<String, Object> insertLineItems(int nInsertsLineitemExpected) throws SQLException {
		Map<String, Object> result = new HashMap<String, Object>();
		calculateInsertsExpected(nInsertsLineitemExpected);
		int nInsertsPartsupp, nInsertsPart, nInsertsNation, nInsertsLineitem,
		nInsertsRegion, nInsertsCustomer, nInsertsOrders, nInsertsSupplier;
		nInsertsPartsupp = nInsertsPart = nInsertsNation = nInsertsLineitem = 
		nInsertsRegion = nInsertsCustomer = nInsertsOrders = nInsertsSupplier = 0;
		
		//Minimum 1 Region and 1 Nation
		nInsertsRegionExpected = nInsertsRegionExpected == 0 ? 1 : nInsertsRegionExpected;
		nInsertsNationExpected = nInsertsNationExpected == 0 ? 1 : nInsertsNationExpected;
		
		//Set result vars
		result.put("lineitemExpected", nInsertsLineitemExpected);
		result.put("partsuppExpected", nInsertsPartsuppExpected);
		result.put("partExpected", nInsertsPartExpected);
		result.put("nationExpected", nInsertsNationExpected);
		result.put("regionExpected", nInsertsRegionExpected);
		result.put("customerExpected", nInsertsCustomerExpected);
		result.put("ordersExpected", nInsertsOrdersExpected);
		result.put("supplierExpected", nInsertsSupplierExpected);
		
		//Set vars actual state
		int lastPkRegion = maxValue("region", "R_RegionKey");
		int lastPkNation = maxValue("nation", "N_NationKey");
		int lastPkCustomer = maxValue("customer", "C_CustKey");
		int lastPkSupplier = maxValue("supplier", "S_SuppKey");
		int lastPkPart = maxValue("part", "P_PartKey");
		int lastPkOrder = maxValue("orders", "O_OrderKey");
		int lastCountPartsupp = count("partsupp");
		
		//Regions
		String sInsert = "INSERT INTO region " +
				"(R_RegionKey,R_Name,R_Comment) " +
				"VALUES (?, ?, ?)";

		PreparedStatement oPst = getConnection().prepareStatement(sInsert);
		
		int nPk = lastPkRegion + 1;
		for (int i = 0; i < nInsertsRegionExpected; i++) {
			oPst.setInt(1, nPk);
			oPst.setString(2, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
			oPst.setString(3, RandomValues.randomString(60));
			try {
				oPst.execute();
				nInsertsRegion++;
			} catch (Exception ex) {
				
			}
			nPk++;
		}
		
		//Nations
		sInsert = "INSERT INTO nation " +
				"(N_NationKey, N_Name,N_RegionKey,N_Comment) " +
				"VALUES (?, ?, ?, ?)";
		
		oPst = getConnection().prepareStatement(sInsert);
		nPk = lastPkNation + 1;
		for (int i = 0; i < nInsertsNationExpected; i++) {
			oPst.setInt(1, nPk);
			oPst.setString(2, RandomValues.randomString(32));
			if (nInsertsRegion > 0) {
				oPst.setInt(3, RandomValues.randomInt(lastPkRegion + 1, lastPkRegion + nInsertsRegion));
			} else {
				oPst.setNull(3, Types.INTEGER);
			}
			oPst.setString(4, RandomValues.randomString(60));
			try {
				oPst.execute();
				nInsertsNation++;
			} catch (Exception ex) {
				
			}
			nPk++;
		}
		
		//Customer
		sInsert = "INSERT INTO customer " +
									"(C_CustKey,C_Name,C_Address,C_NationKey,C_Phone,C_AcctBal,C_MktSegment, C_Comment) " +
									"VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
		 
		oPst = getConnection().prepareStatement(sInsert);
		nPk = lastPkCustomer + 1;
		for (int i = 0; i < nInsertsCustomerExpected; i++) {
			oPst.setInt(1, nPk);
			oPst.setString(2,  RandomValues.randomString(32));
			oPst.setString(3, RandomValues.randomString(32));
			if (nInsertsNation > 0) {
				oPst.setInt(4, RandomValues.randomInt(lastPkNation + 1, lastPkNation + nInsertsNation));
			} else {
				oPst.setNull(4, Types.INTEGER);
			}
			oPst.setString(5, RandomValues.randomString(32));
			oPst.setDouble(6, RandomValues.randomFloat(1000, 1100, 2));
			oPst.setString(7, RandomValues.randomString(32));
			oPst.setString(8, RandomValues.randomString(60));
			try {
				oPst.execute();
				nInsertsCustomer++;
			} catch (Exception ex) {
				
			}
			nPk++;
		}
		
		//Supplier	
		sInsert = "INSERT INTO supplier " +
									"(S_SuppKey,S_Name,S_Address,S_NationKey,S_Phone,S_AcctBal,S_Comment) " +
									"VALUES (?, ?, ?, ?, ?, ?, ?)";
		 
		oPst = getConnection().prepareStatement(sInsert);
		nPk = lastPkSupplier + 1;
		for (int i = 0; i < nInsertsSupplierExpected; i++) {
			oPst.setInt(1, nPk);
			oPst.setString(2,  RandomValues.randomString(32));
			oPst.setString(3, RandomValues.randomString(32));
			if (nInsertsNation > 0) {
				oPst.setInt(4, RandomValues.randomInt(lastPkNation + 1, lastPkNation + nInsertsNation));
			} else {
				oPst.setNull(4, Types.INTEGER);
			}
			oPst.setString(5, RandomValues.randomString(9));
			oPst.setDouble(6, RandomValues.randomFloat(1000, 1100, 2));
			oPst.setString(7, RandomValues.randomString(53));
			try {
				oPst.execute();
				nInsertsSupplier++;
			} catch (Exception ex) {
				
			}
			nPk++;
		}		
		
		
		//Part		
		sInsert = "INSERT INTO part " +
				"(P_PartKey,P_Name,P_Mfgr,P_Brand,P_Type,P_Size,P_Container,P_RetailPrice,P_Comment) " +
				"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

		oPst = getConnection().prepareStatement(sInsert);
		nPk = lastPkPart + 1;
		for (int i = 0; i < nInsertsPartExpected; i++) {
			oPst.setInt(1, nPk);
			oPst.setString(2,  RandomValues.randomString(32));
			oPst.setString(3, RandomValues.randomString(32));
			oPst.setString(4, RandomValues.randomString(32));
			oPst.setString(5, RandomValues.randomString(32));
			oPst.setInt(6, RandomValues.randomInt(1000, 1100));
			oPst.setString(7, RandomValues.randomString(32));
			oPst.setDouble(8, RandomValues.randomFloat(1000, 1100, 2));
			oPst.setString(9, RandomValues.randomString(32));
			try {
				oPst.execute();
				nInsertsPart++;
			} catch (Exception ex) {
			
			}
			nPk++;
		}	
		
		//Partsupp
		ArrayList<Pair> partSuppKeys = new ArrayList<Pair>();
		sInsert = "INSERT INTO partsupp " +
				"(PS_PartKey,PS_SuppKey,PS_AvailQty,PS_SupplyCost, PS_Comment) " +
				"VALUES (?, ?, ?, ?, ?)";

		oPst = getConnection().prepareStatement(sInsert);
		nPk = lastCountPartsupp + 1;
		for (int i = 0; i < nInsertsPartsuppExpected; i++) {
			int regsPart = (nInsertsPartExpected + lastPkPart);
			int nPk2 = (int) (Math.ceil((double) nPk / (double) regsPart));
			int nPk1 = ((nPk - 1) % regsPart)+1;
			partSuppKeys.add(new Pair(nPk2, nPk1));
			oPst.setInt(1, nPk1);
			oPst.setInt(2, nPk2);
			oPst.setInt(3, RandomValues.randomInt(1000, 1100));
			oPst.setDouble(4, RandomValues.randomFloat(1000, 1100, 2));
			oPst.setString(5,  RandomValues.randomString(100));
			try {
				oPst.execute();
				nInsertsPartsupp++;
			} catch (Exception ex) {
					
			}
			nPk++;
		}
		
		//Orders
		
		sInsert = "INSERT INTO orders " +
				"(O_OrderKey,O_CustKey,O_OrderStatus,O_TotalPrice,O_OrderDate,O_OrderPriority,O_Clerk,O_ShipPriority,O_Comment) " +
				"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

		oPst = getConnection().prepareStatement(sInsert);
		nPk = lastPkOrder + 1;
		for (int i = 0; i < nInsertsOrdersExpected; i++) {
			oPst.setInt(1, nPk);
			if (nInsertsCustomer > 0) {
				oPst.setInt(2, RandomValues.randomInt(lastPkCustomer + 1, lastPkCustomer + nInsertsCustomer));
			} else {
				oPst.setNull(2, Types.INTEGER);
			}
			oPst.setString(3, RandomValues.randomString(32));
			oPst.setDouble(4, RandomValues.randomFloat(1000, 1100, 2));
			oPst.setDate(5, RandomValues.randomDate());
			oPst.setString(6, RandomValues.randomString(8));
			oPst.setString(7, RandomValues.randomString(32));
			oPst.setInt(8, RandomValues.randomInt(1000, 1100));
			oPst.setString(9, RandomValues.randomString(40));
			try {
				oPst.execute();
				nInsertsOrders++;
			} catch (Exception ex) {
				System.out.println(ex.getMessage());
			}
			nPk++;
		}
		
		
		//Lineitems		
		sInsert = "INSERT INTO lineitem " +
				"(L_OrderKey,L_PartKey,L_SuppKey,L_LineNumber,L_Quantity,L_ExtendedPrice,L_Discount,L_Tax,L_ReturnFlag,L_LineStatus,L_ShipDate,L_CommitDate,L_ReceiptDate,L_ShipInstruct,L_ShipMode,L_Comment) " +
				"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? ,? ,?)";

		oPst = getConnection().prepareStatement(sInsert);
		int nPkOrders = lastPkOrder + 1;
		for (int i = 0; i < nInsertsOrders; i++) {
			int nLength = partSuppKeys.size();
			for (int j=0; j < nLength; j++) {
				Pair partSuppKey =partSuppKeys.get(j);
				oPst.setInt(1, nPkOrders);
				oPst.setInt(2, partSuppKey.a);
				oPst.setInt(3, partSuppKey.b);
				oPst.setInt(4, nPkOrders % 5);
				oPst.setInt(5, RandomValues.randomInt(1000, 1100));
				oPst.setDouble(6, RandomValues.randomFloat(1000, 1100, 2));
				oPst.setDouble(7, RandomValues.randomFloat(1000, 1100, 2));
				oPst.setDouble(8, RandomValues.randomFloat(1000, 1100, 2));
				oPst.setString(9, RandomValues.randomString(32));
				oPst.setString(10, RandomValues.randomString(32));
				oPst.setDate(11, RandomValues.randomDate());
				oPst.setDate(12, RandomValues.randomDate());
				oPst.setDate(13, RandomValues.randomDate());
				oPst.setString(14, RandomValues.randomString(32));
				oPst.setString(15, RandomValues.randomString(32));
				oPst.setString(16, RandomValues.randomString(32));
				try {
					oPst.execute();
					nInsertsLineitem++;
					if (nInsertsLineitem == nInsertsLineitemExpected) break;
				} catch (Exception ex) {
					System.out.println(ex.getMessage());
				}
				nPkOrders++;
			}
			if (nInsertsLineitem == nInsertsLineitemExpected) break;
		}	
		
		//Set result vars
		result.put("lineitemRegs", nInsertsLineitem);
		result.put("partsuppRegs", nInsertsPartsupp);
		result.put("partRegs", nInsertsPart);
		result.put("nationRegs", nInsertsNation);
		result.put("regionRegs", nInsertsRegion);
		result.put("customerRegs", nInsertsCustomer);
		result.put("ordersRegs", nInsertsOrders);
		result.put("supplierRegs", nInsertsSupplier);
		
		return result;
	}
	
	public int deleteData() {
		int success = 0;
		for (String table : tables) {
			if (execute("DELETE FROM " + table)) success++;
		}
		return success;
	}
	
	protected boolean execute(String sql) {
		try {
			Statement st = getConnection().createStatement();
			st.executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		return true;
	}
	
	protected int maxValue(String table, String field) throws SQLException {
		Statement st = getConnection().createStatement();
		ResultSet rs = st.executeQuery("SELECT MAX(" + field + ") FROM " + table);
		if (rs.next()) return rs.getInt(1);
		return 0;
	}
	
	protected int count(String table) throws SQLException {
		Statement st = getConnection().createStatement();
		ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM " + table);
		if (rs.next()) return rs.getInt(1);
		return 0;
	}
	@Override
	public int query1() {
		String query = "SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty," +
		" sum(l_extendedprice) as sum_base_price, sum(l_extendedprice*(1-l_discount)) as "+
		" sum_disc_price, sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,"+
		" avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) "+
		" as avg_disc, count(*) as count_order"+
		" FROM lineitem"+
		" WHERE l_shipdate <= date '"+lshipdate.toString()+"' "+
		" GROUP BY l_returnflag, l_linestatus"+
		" ORDER BY l_returnflag, l_linestatus";
		System.out.println(query);
		return executeQuery(query);
		
	}
	
	
	@Override
	public int query2() {
		String query = "SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment" +
			" FROM part, supplier, partsupp, nation, region" +
			" WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND p_size = "+psize+" AND p_type like '%"+ptype+"' AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = '"+rname+"' AND ps_supplycost = (SELECT min(ps_supplycost) FROM partsupp, supplier, nation, region WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = '"+rname+"')"  +
			" ORDER BY s_acctbal desc, n_name, s_name, p_partkey";
		System.out.println(query);
		return executeQuery(query);
		
	}
	@Override
	public int query3() {
		String query = "SELECT l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue, o_orderdate, o_shippriority" +
		" FROM customer, orders, lineitem" +
		" WHERE c_mktsegment = '"+cmktsegment+"' AND c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate < date '"+oorderdate+"' AND l_shipdate > date '"+oorderdate2+"'" +
		" GROUP BY l_orderkey, o_orderdate, o_shippriority" +
		" ORDER BY revenue desc, o_orderdate" ;
		System.out.println(query);
		return executeQuery(query);
		
	}
	@Override
	public int query4() {
		String query = "SELECT n_name, sum(l_extendedprice * (1 - l_discount)) as revenue" +
			" FROM customer, orders, lineitem, supplier, nation, region" +
			" WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND l_suppkey = s_suppkey AND c_nationkey = s_nationkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = '"+rname+"' AND o_orderdate >= date '"+oorderdate+"' AND o_orderdate < date '"+oorderdate2+"' + interval '1' year" +
			" GROUP BY n_name" +
			" ORDER BY revenue desc";
		System.out.println(query);
		return executeQuery(query);
	}
	@Override
	public void getDataForQueries() throws SQLException {
		String pAtributes[] = getStrings("part", new String[]{"P_Size", "P_Type"});
		lshipdate = getDate("lineitem", "L_ShipDate", "MAX");
		psize = pAtributes[0];
		ptype = pAtributes[1];
		rname = getString("region", "R_Name");
		oorderdate = getDate("orders", "O_OrderDate", "MAX");
		oorderdate2 = getDate("orders", "O_OrderDate", "MIN");
		cmktsegment = getString("customer", "C_MktSegment");
	}
	
	protected Date getDate(String table, String field, String func) throws SQLException {
		Statement st = getConnection().createStatement();
		ResultSet rs = st.executeQuery("SELECT "+func+"(" + field + ") FROM " + table);
		Date result = null;
		if (rs.next()) result=rs.getDate(1);
		rs.close();
		return result;
	}
	
	protected String getString(String table, String field) throws SQLException {
		return getStrings(table, new String[]{field})[0];
	}
	protected int executeQuery(String query) {
		int count = 0;
		Statement st;
		try {
			st = getConnection().createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next())
				count++;
			rs.close();
			st.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());

		}
		
		return count;
	}
	
	protected String[] getStrings(String table, String fields[]) throws SQLException {
		
		Statement st = getConnection().createStatement();
		String query = "SELECT ";
		for(int i = 0; i < fields.length; ++i) query += fields[i] + (i== fields.length-1 ? "" : ", ");
		query += " FROM " + table;
		ResultSet rs = st.executeQuery(query);
		String result[] = new String[fields.length];
		if (rs.next()) for(int i = 0; i < result.length; ++i)result[i]=rs.getString(i+1);
		rs.close();
		return result;
	}
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	public Connection getConnection() {
		return connection;
	}
}